/**
 * Project: apollo-base-tools
 * 
 * File Created at 2017年8月7日
 * 
 * Copyright 2015-2016 dx.com Croporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * DongXue software Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with dx.com.
 */
package org.apollo.sqlite.core;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apollo.sys.SysLog;

/** 
* @ClassName: SQLiteDBHelper 
* @Description: TODO(这里用一句话描述这个类的作用) 
* @author wuzhenfang(wzfbj2008@163.com)
* @date 2017年8月7日 上午12:27:55 
* @version V1.0 
*/
public class SQLiteDBHelper  {
	
	private final static Logger Log = SysLog.getLogger(SQLiteDBHelper.class);
	

    public SQLiteDBHelper(DBConfig config) {
        super(config.getContext(), config.getDBName(), null, config.getDBVersion());
        this.mDbUpgradeListener = config.getUpgradeListener();
    }

    public SQLiteDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public SQLiteDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    	Log.debug("onUpgrade","onUpgrade DataBase from version " + oldVersion + " to " + newVersion);

        if (mDbUpgradeListener != null) {
            mDbUpgradeListener.onUpgrade(db, oldVersion, newVersion);
        } else {
            // 默认删除表
            deleteAllTables(db);
        }
    }

    /**
     * 注册数据表
     * @param clazz 表的列结构bean
     * @param <T>
     */
    public <T> void registerTable(Class<T> clazz) {
        if (tableHandlers == null) {
            tableHandlers = new ArrayList<>();
        }
        DatabaseHandler handler = new DatabaseHandler<>(clazz);
        if (isValidTable(handler)) {
            tableHandlers.add(handler);
        }
    }
    
    protected void onUpgrade(SQLiteDatabase db, ConnectionSource cs) throws SQLException {
        List<ColumnStruct> oldStruct = DatabaseUtil.getOldTableStruct(db, tableName);
        List<ColumnStruct> newStruct = DatabaseUtil.getNewTableStruct(cs, clazz);

        if (oldStruct.isEmpty() && newStruct.isEmpty()) {
            Log.d(TAG, "数据表结构都为空！不是合法的数据库bean！！！");
        } else if (oldStruct.isEmpty()) {
            Log.d(TAG, "新增表");
            create(cs);
        } else if (newStruct.isEmpty()) {
            // 永远不会执行
            Log.e(TAG, "删除表");
            drop(cs);
        } else {
            dealColumnChange(db, cs, oldStruct, newStruct);
        }
    }

    /**
     * 处理表有变化的情况
     */
    private void dealColumnChange(SQLiteDatabase db, ConnectionSource cs, List<ColumnStruct> oldStruct,
                                  List<ColumnStruct> newStruct) throws SQLException {
        if (DatabaseUtil.hasChangeColumnLimit(oldStruct, newStruct)) {
            Log.d(TAG, "数据表已有字段的描述改变");
            // 已有字段描述改变了，删除旧表，重建新表
            reset(cs);
        } else {
            // 数据表已有字段的描述没有改变
            // 判断列是否有增减
            List<String> oldColumns = DatabaseUtil.getColumnNames(oldStruct);
            List<String> newColumns = DatabaseUtil.getColumnNames(newStruct);
            if (!oldColumns.equals(newColumns)) {
                Log.d(TAG, "表发生了变化");
                // 判断列的变化情况：增加、减少、增减
                List<String> deleteList = DatabaseUtil.getDeleteColumns(oldColumns, newColumns);
                upgradeByCopy(db, cs, getCopyColumns(oldColumns, deleteList));
            } else {
                Log.i(TAG, "表没有发生变化,不需要更新数据表");
            }
        }
    }

    /**
     * 拷贝数据的方式更新
     *
     * @param columns 原始列减去删除的列
     */
    private void upgradeByCopy(SQLiteDatabase db, ConnectionSource cs, String columns) throws SQLException {
        db.beginTransaction();

        String tempTableName = tableName + "_temp";
        String sql = "ALTER TABLE " + tableName + " RENAME TO " + tempTableName;
        try {
            //rename table
            db.execSQL(sql);

            //create table
            try {
                sql = TableUtils.getCreateTableStatements(cs, clazz).get(0);
                db.execSQL(sql);
            } catch (Exception e) {
                e.printStackTrace();
                TableUtils.createTable(cs, clazz);
            }
            sql = "INSERT INTO " + tableName + " (" + columns + ") " +
                    " SELECT " + columns + " FROM " + tempTableName;
            db.execSQL(sql);

            //drop temp table
            sql = "DROP TABLE IF EXISTS " + tempTableName;
            db.execSQL(sql);

            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("upgrade database table struct fail");
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 获取需要复制的列名
     */
    private String getCopyColumns(List<String> oldColumns, List<String> deleteList) {
        StringBuilder columns = new StringBuilder("");
        if (oldColumns == null || deleteList == null) {
            return columns.toString();
        }
        int index = 0;
        // 存在删除集合里的列不添加
        for (String columnName : oldColumns) {
            if (!CollectionUtil.existValue(columnName, deleteList)) {
                if (index == 0) {
                    columns.append("`").append(columnName).append("`");
                } else {
                    columns.append(", ").append("`").append(columnName).append("`");
                }
                index++;
            }
        }
        return columns.toString();
    }

    /**
     * 数据库升级
     */
    public void onUpgrade(SQLiteDatabase db, ConnectionSource cs, int oldVersion, int newVersion) throws SQLException {
        try {
            onUpgrade(db, cs);
        } catch (SQLException e) {
            e.printStackTrace();
            reset(cs);
        }
    }

    /**
     * 数据库降级
     */
    public void onDowngrade(ConnectionSource connectionSource, int oldVersion, int newVersion) throws SQLException {
        reset(connectionSource);
    }

    /**
     * 删除重新创建数据表
     *
     * @throws SQLException
     */
    private void reset(ConnectionSource connectionSource) throws SQLException {
        drop(connectionSource);
        create(connectionSource);
    }

    /**
     * 清除表数据
     *
     * @throws SQLException
     */
    public void clear(ConnectionSource connectionSource) throws SQLException {
        TableUtils.clearTable(connectionSource, clazz);
    }

    /**
     * 创建表
     *
     * @throws SQLException
     */
    public void create(ConnectionSource connectionSource) throws SQLException {
        TableUtils.createTable(connectionSource, clazz);
    }

    /**
     * 删除表
     *
     * @throws SQLException
     */
    public void drop(ConnectionSource connectionSource) throws SQLException {
        TableUtils.dropTable(connectionSource, clazz, true);
    }
    
    /**
     * 删除数据库中所有表
     * @param db
     */
    public void deleteAllTables() {
        String queryAllTableNameSql = SQLBuilder.buildQueryAllTableNamesSql();

        Cursor cursor = db.rawQuery(queryAllTableNameSql, null);
        if (cursor != null) {
            try {
                while (cursor.moveToNext()) {
                    String sql = SQLBuilder.buildDropTableSql(cursor.getString(0));
                    // 执行删除表的sql语句
                    db.execSQL(sql);
                }
            } finally {
                cursor.close();
            }
        }
    }
    
    /**
     * 数据库名称
     */
    public static String DATABASE_NAME = null;

    /**
     * 数据库版本号
     */
    public static int DATABASE_VERSION = -1;

    private static volatile DatabaseHelper instance;

    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        System.setProperty(LocalLog.LOCAL_LOG_LEVEL_PROPERTY, "FATAL");
    }

    public static DatabaseHelper getInstance(Context context) {
        if (null == DATABASE_NAME || DATABASE_VERSION < 0) {
            throw new IllegalStateException("database name or database version not initialize");
        }
        DatabaseHelper inst = instance;
        if (inst == null) {
            synchronized (DatabaseHelper.class) {
                inst = instance;
                if (inst == null) {
                    inst = new DatabaseHelper(context);
                    instance = inst;
                }
            }
        }
        return inst;
    }

    /**
     * 设置数据库名称
     *
     * @param databaseName 数据库名称
     */
    public static void setDbName(String databaseName) {
        DATABASE_NAME = databaseName;
    }

    /**
     * 设置数据库版本号
     *
     * @param databaseVersion 数据库版本号
     */
    public static void setDbVersion(int databaseVersion) {
        DATABASE_VERSION = databaseVersion;
    }
    
    /**
     * 生成新的数据表结构
     *
     * @param connectionSource
     * @param clazz            表的实体bean
     * @param <T>
     * @return 新版本数据库表的列结构集合
     */
    public static <T> List<ColumnStruct> getNewTableStruct(ConnectionSource connectionSource, Class<T> clazz) {
        List<ColumnStruct> columnStruct = new ArrayList<>();
        try {
            String struct = TableUtils.getCreateTableStatements(connectionSource, clazz).get(0);
            columnStruct = getColumnStruct(struct);
        } catch (SQLException e) {
            Log.e(TAG, "create new table statements fail", e);
        }
        return columnStruct;
    }

    /**
     * 查询数据库中对应表的表结构
     *
     * @param db
     * @param tableName 表名
     * @return 旧版本数据库表的列结构集合
     */
    public static List<ColumnStruct> getOldTableStruct(SQLiteDatabase db, String tableName) {
        List<ColumnStruct> columnStruct = new ArrayList<>();
        Cursor cursor = null;
        try {
            cursor = db.rawQuery("select * from sqlite_master where type = ? AND name = ?",
                    new String[]{"table", tableName});
            if (cursor != null) {
                cursor.moveToFirst();
                int columnIndex = cursor.getColumnIndex("sql");
                if (-1 != columnIndex && cursor.getCount() > 0) {
                    String struct = cursor.getString(columnIndex);
                    columnStruct = getColumnStruct(struct);
                } else {
                    Log.i(TAG, "不存在旧表");
                }
            } else {
                Log.i(TAG, "数据库操作失败");
            }
        } catch (Exception e) {
            Log.e(TAG, "create old table statements fail", e);
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
        return columnStruct;
    }

    /**
     * 生成表结构集合
     *
     * @param tableStruct 规范建表语句
     * @return 表结构集合
     */
    public static List<ColumnStruct> getColumnStruct(String tableStruct) {
        List<ColumnStruct> columnStructList = new ArrayList<>();
//      "CREATE TABLE `msg_record` (`id` INTEGER PRIMARY KEY AUTOINCREMENT , `title` VARCHAR ,  UNIQUE (`number`))"
        // 解析过程根据标准的ormlite建表语句设计
        String subString = tableStruct.substring(tableStruct.indexOf("(") + 1, tableStruct.length() - 1);
        String[] sub = subString.split(", ");
        for (String str : sub) {
            if (str.contains("(") || str.contains(")")) {
                str = str.replace("(", "").replace(")", "");
            }
            str = str.trim();//去除字符串的首尾空格
            if (str.startsWith("`")) {
                String[] column = str.split("` ");
                columnStructList.add(new ColumnStruct(column[0].replace("`", ""), column[1]));
            } else {
                // 附加的额外字段限制,不是以`开始
                if (str.contains(",")) {
                    String[] column = str.split(" `");
                    String[] columns = column[1].replace("`", "").split(",");
                    for (String str1 : columns) {
                        modifyColumnStruct(columnStructList, str1, "UniqueCombo");
                    }
                } else {
                    String[] column = str.split(" `");
                    modifyColumnStruct(columnStructList, column[1].replace("`", ""), column[0]);
                }
            }
        }
        return columnStructList;
    }

    /**
     * 修改列结构
     * 部分列结构有多个限制字段，需要进行合并
     *
     * @param list       需要加入的列结构集合
     * @param columnName 需要合并的列名
     * @param limit      额外限制字段
     */
    private static void modifyColumnStruct(List<ColumnStruct> list, String columnName, String limit) {
        if (list == null || list.isEmpty()) {
            Log.e(TAG, "list is null.");
            return;
        }
        if (TextUtils.isEmpty(columnName) || TextUtils.isEmpty(limit)) {
            Log.e(TAG, "columnName is null or limit is null.");
            return;
        }

        int size = list.size();
        for (int i = 0; i < size; i++) {
            ColumnStruct struct = list.get(i);
            if (columnName.equals(struct.getColumnName())) {
                // 列结构集合中已存在对应列，修改对应列结构
                StringBuilder sb = new StringBuilder(struct.getColumnLimit());
                sb.append(" ");
                sb.append(limit);
                struct.setColumnLimit(sb.toString());
                return;
            }
        }
        // 列结构集合中不存在对应列，添加进列结构
        list.add(new ColumnStruct(columnName, limit));
    }

    /**
     * 判断数据表已有字段的限制是否有改变
     *
     * @param oldStructList 旧的表列结构集合
     * @param newStructList 新的表列结构集合
     * @return true：改变；false：未改变
     */
    public static boolean hasChangeColumnLimit(List<ColumnStruct> oldStructList,
                                               List<ColumnStruct> newStructList) {
        if (oldStructList == null || newStructList == null) {
            return false;
        }

        for (ColumnStruct oldStruct : oldStructList) {
            if (existChangeColumn(newStructList, oldStruct)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 判断列结构集合中是否存在与给定列结构列名相同，且列的限制已改变的列
     *
     * @param newStructList 列结构集合
     * @param oldStruct     给定列
     * @return true：存在满足条件的列；false：不存在满足条件的列
     */
    private static boolean existChangeColumn(List<ColumnStruct> newStructList, ColumnStruct oldStruct) {
        if (oldStruct == null) {
            return false;
        }
        String oldName = oldStruct.getColumnName();
        if (TextUtils.isEmpty(oldName)) {
            // 列名异常，当做不满足条件处理
            return false;
        }
        for (ColumnStruct newStruct : newStructList) {
            if (isChangeColumnStruct(oldStruct, newStruct)) {
                return true;
            }
        }
        return false;
    }


    /**
     * 比较列结构之间是否有变化
     *
     * @param oldStruct 比较列
     * @param newStruct 被比较列
     * @return true：改变；false：未改变
     */
    private static boolean isChangeColumnStruct(ColumnStruct oldStruct, ColumnStruct newStruct) {
        if (oldStruct == null || newStruct == null) {
            return false;
        }
        String oldName = oldStruct.getColumnName();
        String oldLimit = oldStruct.getColumnLimit();
        if (oldName == null || !oldName.equals(newStruct.getColumnName())) {
            // 不是同一字段
            return false;
        }
        // 比较同一字段的差异
        String newLimit = newStruct.getColumnLimit();
        if (oldLimit == null && newLimit == null) {
            // 都没有限制符
            return false;
        } else {
            // 某一个添加了限制符 或者限制符被改变
            return oldLimit == null || newLimit == null || !oldLimit.equals(newLimit);
        }
    }

    /**
     * 获得数据表中已删除字段
     *
     * @param oldColumns 旧的表列结构集合
     * @param newColumns 新的表列结构集合
     * @return 已删除字段集合
     */
    public static List<String> getDeleteColumns(List<String> oldColumns, List<String> newColumns) {
        return getColumnChange(oldColumns, newColumns);
    }

    /**
     * 获取已更改的列集合
     *
     * @param oldColumns 旧的表列结构集合
     * @param newColumns 新的表列结构集合
     * @return 返回oldColumns中存在，而newColumns中不存在的列集合
     */
    private static List<String> getColumnChange(List<String> oldColumns, List<String> newColumns) {
        List<String> columnList = new ArrayList<>();
        if (oldColumns == null || newColumns == null) {
            return columnList;
        }

        boolean exist;
        for (String oldColumn : oldColumns) {
            exist = false;
            for (String newColumn : newColumns) {
                if (newColumn != null && newColumn.equals(oldColumn)) {
                    // 存在对应的字段
                    exist = true;
                    break;
                }
            }
            if (!exist) {
                // 字段不匹配
                columnList.add(oldColumn);
            }
        }
        return columnList;
    }

    /**
     * 集合中是否存在指定列
     */
    public static ColumnStruct existColumn(String columnName, List<ColumnStruct> list) {
        if (list == null || columnName == null) {
            return null;
        }
        for (ColumnStruct struct : list) {
            if (struct == null) {
                continue;
            }
            if (columnName.equals(struct.getColumnName())) {
                return struct;
            }
        }
        return null;
    }
}
